﻿using System.Text;
using Dapper;
using Microsoft.Data.SqlClient;
using Production.CommonLib.Models;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace Production.ApplicationLib.Queries
{
    public class StandardBomQueries : IStandardBomQueries
    {
        private readonly string? _defaultConnStr;

        public StandardBomQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }

        public async Task<Result<IList<StandardBomInfoResponseDto>>?> QueryStandardBoms(
            StandardBomSearchRequestDto model)
        {
            IEnumerable<StandardBomInfoResponseDto>? data = null;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryAsync<StandardBomInfoResponseDto>(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProductId,
       t.ProductCode,
       t.ProductName,
       t.Quality,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_STANDARDBOM t where 1 = 1 ");
            }

            return await Result<IList<StandardBomInfoResponseDto>>.SuccessAsync(data.ToList());
        }

        public async Task<PaginatedResult<StandardBomInfoResponseDto>?> QueryStandardBomsByPage(
            StandardBomSearchRequestDto model)
        {
            IEnumerable<StandardBomInfoResponseDto>? data = null;
            int totalCount = 0;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProductId,
       t.ProductCode,
       t.ProductName,
       t.Quality,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_STANDARDBOM t where 1 = 1 ");

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());
                data = await db.QueryAsync<StandardBomInfoResponseDto>(query.SqlPage);

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount);
            }

            return await PaginatedResult<StandardBomInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }

        public async Task<Result<StandardBomInfoResponseDto>?> GetStandardBom(int id)
        {
            StandardBomInfoResponseDto? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryFirstOrDefaultAsync<StandardBomInfoResponseDto>(@"
select t.id,
       t.code,
       t.name,
       t.productId,
       t.productCode,
       t.productName,
       t.quality,
       t.description,
       t.datastate,
       t.createdby,
       t.createdon,
       t.lastmodifiedby,
       t.lastmodifiedon,
       t.deletedby,
       t.deletedon,
       t.productid,
       t.productcode,
       t.productname,
       t.version,
       t.contract
from MES_STANDARDBOM t where 1 = 1 and Id = @id ", new { id });

                if (data != null)
                {
                    data.Items = await db.QueryAsync<StandardBomDetailInfoResponseDto>(@"
select t.id,
       t.materialid,
       t.materialcode,
       t.materialname,
       t.standardbomid,
       t.quality,
       t.lossrate,
       t.createdby,
       t.createdon,
       t.lastmodifiedby,
       t.lastmodifiedon,
       t.deletedby,
       t.deletedon
from MES_STANDARDBOM_DETAIL t where 1 = 1 and standardbomid = @standardbomid", new { standardbomid = data.Id });
                }
            }

            return await Result<StandardBomInfoResponseDto>.SuccessAsync(data);
        }
    }
}